/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.myapp.struts.dao;

//~--- non-JDK imports --------------------------------------------------------
import com.myapp.struts.prepare.PaymentsPrepareAL;
//~--- JDK imports ------------------------------------------------------------

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author pavan
 */
public class PaymentsVoucerDao {

    private PaymentsPrepareAL ipl = new PaymentsPrepareAL();
    private ArrayList al;
    private boolean b;
    private Connection con;
    private Connect drv;
    private int i, j;
    private PreparedStatement pstmt;
    private ResultSet rs;
    private String updateAchead;
    private String updateCash;
    private String updateTypeAc;
    private String checkAcNature;
    private ResultSet acnature;
    private String resacnature;
    private String updateledger;
    private int ij;
    private int ik;
    private double closingBalance;
    private double cb;

    public Connection getConnect() throws Exception {
        drv = new Connect();
        con = drv.mySql();
        return con;
    }

    public boolean insert(String pno, String ptype, String achead, String date1, String amt, String nar, String cby,
            String cdate, String status) {
        try {
            con = getConnect();
            con.setAutoCommit(false);

            String ss = "INSERT INTO payments VALUES(?,?,?,?,?,?,?,?,?)";

            pstmt = con.prepareStatement(ss);
            pstmt.setString(1, pno);
            pstmt.setString(2, ptype);
            pstmt.setString(3, achead);
            pstmt.setString(4, date1);
            pstmt.setString(5, amt);
            pstmt.setString(6, nar);
            pstmt.setString(7, cby);
            pstmt.setString(8, cdate);
            pstmt.setString(9, status);
            i = pstmt.executeUpdate();
            pstmt.close();
            if (i == 1) {
                System.out.println("inside InvoiceDao values inserted");
                System.out.println("----------------------------");
                checkAcNature = "select Description from account_head where Ahead_Name=?";
                pstmt = con.prepareStatement(checkAcNature);
                pstmt.setString(1, achead);
                acnature = pstmt.executeQuery();

                if (acnature.next() == true) {
                    resacnature = acnature.getString(1);
                }
                pstmt.close();
                if (resacnature.equals("Cr")) {

                    /** ----------------------------------------------------------- */
                    updateAchead = "update account_head set Opening_Balance=Opening_Balance-" + amt + " where Ahead_Name='" + achead + "'";
                    System.out.println("updateAchead" + updateAchead);
                    pstmt = con.prepareStatement(updateAchead);

                    j = pstmt.executeUpdate();
                    pstmt.close();
                    if (j == 1) {
                        updateTypeAc = "update account_head set Opening_Balance=Opening_Balance-" + amt + " where  Ahead_Name='" + ptype + "'";
                        System.out.println("J1====" + updateTypeAc);
                        pstmt = con.prepareStatement(updateTypeAc);

                        int k = pstmt.executeUpdate();
                        pstmt.close();
                        if (k == 1) {
                            /** ******************************** */
                            cb = getClosingBalance(ptype);
                            updateledger = "insert into ledger values(?,?,?,?,?,?,?,?,?)";
                            pstmt = con.prepareStatement(updateledger);
                            pstmt.setString(1, null);
                            pstmt.setString(2, pno);
                            pstmt.setString(3, ptype);
                            pstmt.setString(4, date1);
                            pstmt.setString(5, "Cr");
                            pstmt.setString(6, achead);
                            pstmt.setString(7, "PV");
                            pstmt.setString(8, amt);
                            pstmt.setDouble(9, (cb - Double.parseDouble(amt)));
                            ij = pstmt.executeUpdate();
                            pstmt.close();
                            if (ij > 0) {
                                cb = getClosingBalance(achead);
                                pstmt = con.prepareStatement(updateledger);
                                pstmt.setString(1, null);
                                pstmt.setString(2, pno);
                                pstmt.setString(3, achead);
                                pstmt.setString(4, date1);
                                pstmt.setString(5, "Dr");
                                pstmt.setString(6, ptype);
                                pstmt.setString(7, "PV");
                                pstmt.setString(8, amt);
                                pstmt.setDouble(9, (cb + Double.parseDouble(amt)));
                                ik = pstmt.executeUpdate();
                                pstmt.close();
                            }
                            if (ik > 0) {
                                b = true;
                            }
                            /** ************************** */
                        } else {
                            b = false;
                        }
                    } else {
                        b = false;
                    }

                    /** ----------------------------------------------------------- */
                } else if (resacnature.equals("Dr")) {
                    updateAchead = "update account_head set Opening_Balance=Opening_Balance+" + amt + " where Ahead_Name='" + achead + "'";
                    System.out.println("updateAchead" + updateAchead);
                    pstmt = con.prepareStatement(updateAchead);

                    j = pstmt.executeUpdate();

                    if (j == 1) {
                        updateTypeAc = "update account_head set Opening_Balance=Opening_Balance-" + amt + " where  Ahead_Name='" + ptype + "'";
                        System.out.println("J1====" + updateTypeAc);
                        pstmt = con.prepareStatement(updateTypeAc);

                        int k = pstmt.executeUpdate();

                        if (k == 1) {
                            /** ******************************** */
                            cb = getClosingBalance(ptype);
                            updateledger = "insert into ledger values(?,?,?,?,?,?,?,?,?)";
                            pstmt = con.prepareStatement(updateledger);
                            pstmt.setString(1, null);
                            pstmt.setString(2, pno);
                            pstmt.setString(3, ptype);
                            pstmt.setString(4, date1);
                            pstmt.setString(5, "Cr");
                            pstmt.setString(6, achead);
                            pstmt.setString(7, "PV");
                            pstmt.setString(8, amt);
                            pstmt.setDouble(9, (cb + Double.parseDouble(amt)));
                            ij = pstmt.executeUpdate();
                            pstmt.close();
                            if (ij > 0) {
                                cb = getClosingBalance(achead);
                                pstmt = con.prepareStatement(updateledger);
                                pstmt.setString(1, null);
                                pstmt.setString(2, pno);
                                pstmt.setString(3, achead);
                                pstmt.setString(4, date1);
                                pstmt.setString(5, "Dr");
                                pstmt.setString(6, ptype);
                                pstmt.setString(7, "PV");
                                pstmt.setString(8, amt);
                                pstmt.setDouble(9, (cb - Double.parseDouble(amt)));
                                ik = pstmt.executeUpdate();
                            }
                            if (ik > 0) {
                                b = true;
                            }
                            /** ************************** */
                        } else {
                            b = false;
                        }
                    } else {
                        b = false;
                    }
                }
            } else {
                b = false;
            }
            con.commit();
            pstmt.close();
            con.close();
        } catch (Exception ex) {
            Logger.getLogger(ReceiptsVoucerDao.class.getName()).log(Level.SEVERE, null, ex);
        }

        return b;
    }

    public ArrayList editlist(String trnsno) throws Exception {
        try {
            con = getConnect();
            System.out.println("trnsno is  ..........." + trnsno);

            // System.out.println(con);
            String query =
                    "select Payment_No,Payment_Type,Account_head,Payment_Date,sum(Amount),Narration from payments where Payment_Status=1 and Payment_No =? group by Payment_No";

            // System.out.println("AFTER THE QUERY "+query);
            pstmt = con.prepareStatement(query);

            // System.out.println("values of prepared statement  "+pstmt);
            pstmt.setString(1, trnsno);
            rs = pstmt.executeQuery();
            System.out.println("RS IS ..................." + rs);
            al = ipl.prepareArrayList(rs);
            System.out.println("al is not null" + al);
            rs.close();
            pstmt.close();
            con.close();
        } // end of try
        catch (Exception e) {
            System.out.println("\n exception at retrieving the data" + e);
        }    // end of catch

        return al;
    }

    public ArrayList list() throws Exception {
        try {
            System.out.println("inside list method at PaymentsDao");
            con = getConnect();
            ipl = new PaymentsPrepareAL();
            System.out.println(con);

            String query =
                    "select Payment_No,Payment_Type,Account_head,Payment_Date,sum(Amount),Narration from payments where Payment_Status=1 group by Payment_No";

            pstmt = con.prepareStatement(query);
            rs = pstmt.executeQuery();
            al = ipl.prepareArrayList(rs);
            rs.close();
            pstmt.close();
            con.close();
        } // end of try
        catch (Exception e) {
            System.out.println("exception at list method");
        }    // end of catch

        return al;
    }

    public ArrayList getPaymentsList(String achead) throws Exception {
        try {
            con = getConnect();
            System.out.println("achead is  ...........1" + achead);

            // System.out.println(con);
            String query =
                    "select Payment_No,Payment_Type,Account_head,Payment_Date,Amount," +
                    "Narration from payments where Payment_Status=1 and Account_Head ='" + achead +
                    "'";
            System.out.println("1----->" + query);
            // System.out.println("AFTER THE QUERY "+query);
            pstmt = con.prepareStatement(query);

            // System.out.println("values of prepared statement  "+pstmt);
            //pstmt.setString(1, achead);
            rs = pstmt.executeQuery();
            System.out.println("RS IS ..................." + rs);
            if (rs.next() == true) {
                rs.beforeFirst();
                al = ipl.prepareArrayList(rs);
            } else {
                return null;
            }
            System.out.println("al is not null" + al);
            rs.close();
            pstmt.close();
            con.close();
        } // end of try
        catch (Exception e) {
            System.out.println("\n exception at retrieving the data" + e);
        }    // end of catch

        return al;
    }

    public ArrayList getPaymentsListByType(String achead) throws Exception {
        try {
            con = getConnect();
            System.out.println("achead is  ...........2" + achead);
            // System.out.println(con);
            String query =
                    "select Payment_No,Payment_Type,Account_head,Payment_Date,Amount," +
                    "Narration from payments where Payment_Status=1 and Payment_Type='" + achead +
                    "' order by Payment_Date ASC";
            System.out.println("2----->" + query);
            // System.out.println("AFTER THE QUERY "+query);
            pstmt = con.prepareStatement(query);

            // System.out.println("values of prepared statement  "+pstmt);
            //pstmt.setString(1, achead);
            rs = pstmt.executeQuery();
            System.out.println("RS IS ..................." + rs);
            if (rs.next() == true) {
                rs.beforeFirst();
                al = ipl.prepareArrayList(rs);
            } else {
                return null;
            }
            System.out.println("al is not null" + al);
            rs.close();
            pstmt.close();
            con.close();
        } // end of try
        catch (Exception e) {
            System.out.println("\n exception at retrieving the data" + e);
        }    // end of catch

        return al;
    }

    public double getClosingBalance(String achead) throws Exception {
        try {
            System.out.println("closing is  ..........." + achead);

            // System.out.println(con);
            String query = "select closingbalance from ledger where id in(" +
                    "select max(id) from ledger where accounthead=? " +
                    "  order by Receipt_Date ASC)";
            System.out.println("2----->" + query);
            // System.out.println("AFTER THE QUERY "+query);
            pstmt = con.prepareStatement(query);

            // System.out.println("values of prepared statement  "+pstmt);
            pstmt.setString(1, achead);
            rs = pstmt.executeQuery();
            System.out.println("RS IS ..................." + rs);

            if (rs.next() == true) {
                closingBalance = rs.getDouble(1);
            } else {
                closingBalance = 0;
            }
            rs.close();
            pstmt.close();
        } catch (Exception e) {
            System.out.println("\n exception at retrieving the data" + e);
        }    // end of catch

        return closingBalance;
    }
}
